package cn.itcast.employmanager.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.sun.org.apache.regexp.internal.recompile;

import cn.itcast.employmanager.domain.Employee;
import cn.itcast.employmanager.utils.C3p0Util;

public class EmployeeDAO {
	//添加员工方法 
	public void addEmploee(Employee employee) throws SQLException {
		QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
		String sql="insert into employee (`name`,`phone`,`department_id`) values (?,?,?)";
		runner.update(sql, employee.getName(),employee.getPhone(),employee.getDepartmentId());		
		
	}
	//获取员工列表方法
	public List<Employee> findAllEmployees() throws SQLException {
		QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
		String sql="select e.*,d.name as departmentName "
				+ "from employee e left join department d "
				+ "on e.department_id = d.id "
				+ "order by e.id";
		List<Employee> list = runner.query(sql, new BeanListHandler<Employee>(Employee.class));
		return list;
		
	}
	
	//删除员工
	public void deleteEmployee(Integer employId) throws SQLException {
		QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
		String sql="delete from employee where id=?";
		runner.update(sql, employId);
	}
	
	//获取某个员工
	public Employee findEmployee(Integer employId) throws SQLException {
		QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
		String sql="select `id`,`name`,`phone`,`department_id` as departmentId  from employee where id=?";
		Employee employee = runner.query(sql,new BeanHandler<Employee>(Employee.class), employId);
		return employee;
	}
	
	//修改员工方法 
		public void updateEmploee(Employee employee) throws SQLException {
			QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
			String sql="update employee set `name` = ?,`phone` = ?,`department_id` = ? where `id`=?";
			runner.update(sql, 
					employee.getName(),employee.getPhone(),employee.getDepartmentId(),employee.getId());		
			
		}
}
